<?php

class Categories_M extends I_Model {

    protected $_table_name = 'categorias';
    protected $_primary_key = 'ID';
    protected $_primary_filter = 'intval';
    protected $_order_by = 'ORDER';
    public $rules = array();
    protected $_timestamps = FALSE;

    public function __construct() {
        parent::__construct();
    }

    function get($id = null, $single = false) {
        $this->db->select('ID as COD,NAME,SLUG,ORDER');
        return parent::get($id, $single);
    }
    
    
    
    function get_count_lotes(){
    	 
    	$this->db->select('cat.NAME,cat.SLUG,cat.ID AS CAT_ID,tpi.TOTAL')
    	->from('categorias as cat')
    	->join('(SELECT CATEGORIA_ID,COUNT(DISTINCT lt.ID) AS TOTAL 
    			     FROM tipos_ativos as tp 
                     LEFT JOIN lotes as lt ON lt.TIPO_ID=tp.ID
    			     LEFT JOIN leiloes as le ON lt.LEILAO_ID=le.ID
    			     WHERE le.STATUS = "1" OR  le.STATUS = "2" 
    			     GROUP BY CATEGORIA_ID    
                  )as tpi','tpi.CATEGORIA_ID=cat.ID','LEFT')
    	->group_by('cat.NAME','asc'); 
     
    $q = $this->db->get();
    
    
    	
    return $q->result();
    }
    function get_count_lotes_closed(){
    	 
    	$this->db->select('cat.NAME,cat.SLUG,cat.ID AS CAT_ID,tpi.TOTAL')
    	->from('categorias as cat')
    	->join('(SELECT CATEGORIA_ID,COUNT(DISTINCT lt.ID) AS TOTAL 
    			     FROM tipos_ativos as tp 
                     LEFT JOIN lotes as lt ON lt.TIPO_ID=tp.ID
    			     LEFT JOIN leiloes as le ON lt.LEILAO_ID=le.ID
    			     WHERE le.STATUS = "4" AND le.DATA >= "'.date('Y-m-d',(time() - (20 * 86400))).'"
    			     GROUP BY CATEGORIA_ID    
                  )as tpi','tpi.CATEGORIA_ID=cat.ID','LEFT')
    	->group_by('cat.NAME','asc'); 
     
    $q = $this->db->get();
    
    
    	
    return $q->result();
    }
    
    
    function get_with_subcategories($id = null,$slug = null,$uf = null){
    	$where = '';
    	$join = '';
    	if(!is_null($id)){
    		$where = ' AND le.ID='.$id;
    	}
    	$swhere = '';
    	if(!is_null($slug)){
    		$this->db->where('cat.SLUG',$slug);
    	}
    	
    	if(!is_null($uf)){
    		
    	   $join  = ' JOIN cidades as cid ON cid.ID=lt.CIDADE';
    	   $join .= ' JOIN estados as uf ON cid.ESTADO=uf.ID';
    	   
    	   $where.= ' AND uf.UF = "'.$uf.'"';
    	}
    	$this->db->select('cat.NAME,cat.SLUG,cat.ID AS CAT_ID,tpi.TOTAL')
    	->from('categorias as cat')
    	->join('(SELECT CATEGORIA_ID,COUNT(DISTINCT lt.ID) AS TOTAL
    			     FROM tipos_ativos as tp
                     JOIN lotes as lt ON lt.TIPO_ID=tp.ID
    			     JOIN leiloes as le ON lt.LEILAO_ID=le.ID
    			     '.$join.'
    			     WHERE (le.STATUS = "1" OR  le.STATUS = "2" ) '.$where.'
    			     GROUP BY CATEGORIA_ID
                  )as tpi','tpi.CATEGORIA_ID=cat.ID');
                  
        
    	
    	$this->db->group_by('cat.NAME','asc');
    	 
    	$q = $this->db->get();
           $data =  $q->result();
         if($q->num_rows() > 0){
         	          $idata = array(); 
         	          foreach ($data as $key => $row):
         	                   if(!isset($row->SUB_CATEGORIES)){
         	                   	$row->SUB_CATEGORIES = array();
         	                   } 

         	                   $this->db->select('tp.*,COUNT(DISTINCT tp.ID) AS TOTAL')
         	                           ->from('tipos_ativos as tp')
         	                            ->join('lotes as lt','lt.TIPO_ID=tp.ID')
         	                            ->join('leiloes as le','lt.LEILAO_ID=le.ID')
         	                            ->where('(le.STATUS = "1" OR  le.STATUS = "2") AND tp.CATEGORIA_ID = '.$row->CAT_ID.'
         	                            		');
         	                   
         	                   $row->SUB_CATEGORIES = $this->db->get()->result();
                             $idata[]= $row;  
         	          endforeach;
         	          return $idata;
         }
    	 
    	return $data ;
    }




     function get_with_subcategories_search($search){
         $like = "`DESCRICAO` LIKE '%{$search}%' OR `TAGS` LIKE '%{$search}%' OR `SUB_DESCRICAO` LIKE '%{$search}%' OR `DESCRICAO_DETALHADA` LIKE '%{$search}%'";
        $this->db->select('cat.NAME,cat.SLUG,cat.ID AS CAT_ID,tpi.TOTAL')
        ->from('categorias as cat')
        ->join('(SELECT CATEGORIA_ID,COUNT(DISTINCT lt.ID) AS TOTAL
                     FROM tipos_ativos as tp
                     JOIN (SELECT * FROM lotes WHERE '.$like.' )  as lt ON lt.TIPO_ID=tp.ID
                     JOIN leiloes as le ON lt.LEILAO_ID=le.ID
                    
                     
                     GROUP BY CATEGORIA_ID
                  )as tpi','tpi.CATEGORIA_ID=cat.ID')
        ;
                  
        
        
        $this->db->group_by('cat.NAME','asc');
         
        $q = $this->db->get();
           $data =  $q->result();
         if($q->num_rows() > 0){
                      $idata = array(); 
                      foreach ($data as $key => $row):
                               if(!isset($row->SUB_CATEGORIES)){
                                $row->SUB_CATEGORIES = array();
                               } 

                               $this->db->select('tp.*,COUNT(DISTINCT tp.ID) AS TOTAL')
                                       ->from('tipos_ativos as tp')
                                        ->join('lotes as lt','lt.TIPO_ID=tp.ID')
                                        ->join('leiloes as le','lt.LEILAO_ID=le.ID')
                                        ->where('(le.STATUS = "1" OR  le.STATUS = "2") AND tp.CATEGORIA_ID = '.$row->CAT_ID.'
                                                ');
                               
                               $row->SUB_CATEGORIES = $this->db->get()->result();
                             $idata[]= $row;  
                      endforeach;
                      return $idata;
         }
         
        return $data ;
    }
}
